IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InjectArraAgreementData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InjectArraAgreementData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
CREATE Procedure [dbo].[InjectArraAgreementData] 
/*
**	Injects all the Arra proposal/agreement/subcontractor data from InfoEd.  This includes the following
**  tables:
**		ArraAgreement
**		ArraRecipient
**		ArraPrimaryPerformanceLocation
**		ArraSubAward
**		ArraHighlyCompensatedPerson
**		ArraGovContractOffice
*/
As
Begin

Set nocount on

Begin Try 

Begin Transaction InjectArraAgreementData

/*-----------------------------------------------------------------------------------
Get the Quarter End Date that is staged.
-----------------------------------------------------------------------------------*/
declare @QuarterEndDate datetime
set @QuarterEndDate = (select top 1 qs.QuarterEndDate from [dbo].[ArraQuarterlyReportStatus] qs
						inner join [dbo].[Stg_IE_Arra_ProposalData] s on s.ArraQuarterlyReportStatusID = qs.ID)

/*-----------------------------------------------------------------------------------
Get the last GL Close data.
-----------------------------------------------------------------------------------*/
declare @LastGLCloseDate datetime
Create table #RefreshDateTable(glPostAsOfDate datetime, salaryAsOfDate datetime
								, poapAsOfDate datetime, a  datetime)
insert into #RefreshDateTable exec getrefreshdates
select	@LastGLCloseDate		= glPostAsOfDate from #RefreshDateTable

/*-----------------------------------------------------------------------------------
Scrape New Recipients from staging
-----------------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraRecipient] ON

INSERT INTO [dbo].[ArraRecipient]([ID],[DunsNumber]
,LegalName
,DBAName
,Address1
,Address2
,Address3
,City
,[State]
,ZipCode
,Country
,CongressionalDistrict
)
Select distinct s.NewArraRecipientID,replace(s.RecipientDunsNumber,'-','')
,s.RecipientLegalName
,s.RecipientDBAName
,s.RecipientAddress1
,s.RecipientAddress2
,s.RecipientAddress3
,s.RecipientCity
,s.RecipientState
,replace(s.RecipientZip,'-','')
,left(s.RecipientCounty,2)
,right(s.RecipientCongressionalDistrict,2)
From [dbo].[Stg_IE_ARRA_ProposalData] s 
where s.NewArraRecipientID is not null and coalesce(RecipientDunsNumber,'')<>''
	and coalesce(s.NewArraAgreementID,s.ArraAgreementID) = (select max(coalesce(s1.NewArraAgreementID,s1.ArraAgreementID))
															from [dbo].[Stg_IE_ARRA_ProposalData] s1
															where  replace(s1.RecipientDunsNumber,'-','') = replace(s.RecipientDunsNumber,'-',''))

Set Identity_insert [dbo].[ArraRecipient] OFF

/*-----------------------------------------------------------------------------------
Update Recipients from staging
-----------------------------------------------------------------------------------*/
Update r
set 
[DunsNumber]	= replace(s.RecipientDunsNumber,'-','')
,LegalName		= s.RecipientLegalName
,DBAName		= s.RecipientDBAName
,Address1		= s.RecipientAddress1
,Address2		= s.RecipientAddress2
,Address3		= s.RecipientAddress3
,City			= s.RecipientCity
,[State]		= s.RecipientState
,ZipCode		= replace(s.RecipientZip,'-','')
,Country		= left(s.RecipientCounty,2)
,CongressionalDistrict = right(s.RecipientCongressionalDistrict,2)
From [dbo].[Stg_IE_ARRA_ProposalData] s 
inner join dbo.ArraRecipient r on r.id = s.ArraRecipientID
where s.ArraRecipientID is not null and coalesce(RecipientDunsNumber,'')<>''

/*-----------------------------------------------------------------------------------
Scrape New Sub Recipients from staging
-----------------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraRecipient] ON

INSERT INTO [dbo].[ArraRecipient]([ID],[DunsNumber],[CongressionalDistrict])
Select distinct s.NewArraRecipientID,replace(s.SubRecipientDunsNumber,'-','')
,right(s.SubRecipientCongressionalDistrict,2)
From [dbo].[Stg_IE_ARRA_SubContractorData] s where s.NewArraRecipientID is not null
	and coalesce(s.SubRecipientDunsNumber,'')<>'' 
	and coalesce(s.NewArraSubAwardID,s.ArraSubAwardID) = (select max(coalesce(s1.NewArraSubAwardID,s1.ArraSubAwardID) ) 
								from [dbo].[Stg_IE_ARRA_SubContractorData] s1
								where replace(s1.SubRecipientDunsNumber,'-','') = replace(s.SubRecipientDunsNumber,'-',''))


Set Identity_insert [dbo].[ArraRecipient] OFF
/*-----------------------------------------------------------------------------------
Update Sub Recipients from staging
-----------------------------------------------------------------------------------*/
Update r
set 
 CongressionalDistrict = right(s.SubRecipientCongressionalDistrict,2)
From [dbo].[Stg_IE_ARRA_SubContractorData] s 
inner join dbo.ArraRecipient r on r.id = s.ArraRecipientID
where s.ArraRecipientID is not null and coalesce(SubRecipientDunsNumber,'')<>''

/*-----------------------------------------------------------------------------------
Scrape New Government Contract Office Codes from staging
-----------------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraGovContractOffice] ON

INSERT INTO [dbo].[ArraGovContractOffice](ID,[Code],[Name])
Select distinct s.NewArraGovContractOfficeID,s.GovernmentContractOfficeCode,s.GovernmentContractOfficeName 
From [dbo].[Stg_IE_ARRA_ProposalData] s where s.NewArraGovContractOfficeID is not null
and coalesce(s.GovernmentContractOfficeCode,'')<>''

Set Identity_insert [dbo].[ArraGovContractOffice] OFF

/*-----------------------------------------------------------------------------------
Scrape New Primary Performance Location from staging
-----------------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraPrimaryPerformanceLocation] ON

INSERT INTO [dbo].[ArraPrimaryPerformanceLocation](ID, [StreetAddress1],[StreetAddress2]
,[City],[State],[Zip],[Country],[CongressionalDistrict])
Select distinct s.NewArraPrimaryPerformanceLocationID,s.PrimaryPerformanceStreet1,s.PrimaryPerformanceStreet2
	,s.PrimaryPerformanceCity, s.PrimaryPerformanceState, replace(s.PrimaryPerformanceZip,'-','')
	, left(s.PrimaryPerformanceCountry,2), right(s.PrimaryPerformanceCongressDistrict,2)
From [dbo].[Stg_IE_ARRA_ProposalData] s  where s.NewArraPrimaryPerformanceLocationID is not null
and coalesce(s.PrimaryPerformanceStreet1,'')<>''

Set Identity_insert [dbo].[ArraPrimaryPerformanceLocation] OFF

/*-----------------------------------------------------------------------------------
Scrape New Sub Primary Performance Location from staging
-----------------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraPrimaryPerformanceLocation] ON

INSERT INTO [dbo].[ArraPrimaryPerformanceLocation](ID, [StreetAddress1],[StreetAddress2]
,[City],[State],[Zip],[Country],[CongressionalDistrict])
Select distinct s.NewArraSubPrimaryPerformanceLocationID,s.SubPrimaryPerformanceAddress1,s.SubPrimaryPerformanceAddress2
	,s.SubPrimaryPerformanceCity, s.SubPrimaryPerformanceState, replace(s.SubPrimaryPerformanceZip,'-','')
	, left(s.SubPrimaryPerformanceCountry,2), right(s.SubPrimaryPerformanceCongressDistrict,2)
From [dbo].[Stg_IE_ARRA_SubContractorData] s  where s.NewArraSubPrimaryPerformanceLocationID is not null
and coalesce(s.SubPrimaryPerformanceAddress1,'')<>''

Set Identity_insert [dbo].[ArraPrimaryPerformanceLocation] OFF

/*-----------------------------------------------------------------------------
Update existing ARR Agreement data
-----------------------------------------------------------------------------*/
Update a
Set FundingArraGovAgencyID	= coalesce(s.FundingArraGovAgencyID,s.NewFundingArraGovAgencyID)
,AwardingArraGovAgencyID	= coalesce(s.AwardingArraGovAgencyID,s.NewAwardingArraGovAgencyID)
,ArraProgramSourceID		= s.ArraProgramSourceID
,OrderNumber				= s.OrderNumber
,CFDANumber					= s.CFDA
,ArraRecipientID			= coalesce(s.ArraRecipientID,s.NewArraRecipientID)
,ParentArraRecipientID		= coalesce(s.ArraRecipientID,s.NewArraRecipientID)
,ArraGovContractOfficeID	= coalesce(s.ArraGovContractOfficeID,s.NewArraGovContractOfficeID)
,[AwardType]				= replace(s.[AwardType],'Subcontract','Grant')
,[AwardDate]				= s.[NogaIssueDate]
,[ProjectStatus]			=
case when coalesce(prev.monthendingdate,'1/1/1900') <= @LastGLCloseDate then 
	case 
		When coalesce(expenses.AmountToDate+Encumbrance.Amount,0) = 0	
													Then 'Not Started'
													
		when coalesce(expenses.AmountToDate,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0))
													Then 'Fully Completed'
		
		when coalesce(expenses.AmountToDate,0) <> 0 
			and coalesce(expenses.AmountToDate,0) < (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Less than 50% completed'
													
		when coalesce(expenses.AmountToDate,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Completed 50% or more'
	end
Else
	case 
		When coalesce(prev.ExpenseAmount,0) = 0	
													Then 'Not Started'
													
		when coalesce(prev.ExpenseAmount,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0))
													Then 'Fully Completed'
		
		when coalesce(prev.ExpenseAmount,0) <> 0 
			and coalesce(prev.ExpenseAmount,0) < (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Less than 50% completed'
													
		when coalesce(prev.ExpenseAmount,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Completed 50% or more'
	end
End
,ArraActivityID				= coalesce(s.ArraActivityID,s.NewArraActivityID)
,ArraPrimaryPerformanceLocationID = coalesce(s.ArraPrimaryPerformanceLocationID,s.NewArraPrimaryPerformanceLocationID)
,AwardedAmount				= coalesce(s.AmountOfAward,0)
,RevenueAmount				= case when coalesce(prev.monthendingdate,'1/1/1900') <= @LastGLCloseDate then Revenue.AmountToDate
								else
									prev.RevenueAmount 
								end
,ExpenseAmount				= case when coalesce(prev.monthendingdate,'1/1/1900') <= @LastGLCloseDate then expenses.AmountToDate
								else
									prev.ExpenseAmount 
								end
,IsReportingApplicable		= 0
,NumberOfSubAwards			= coalesce(s.TotalNumberSubAards,0)
,SubAwardAmount				= coalesce(s.TotalAmountSubAards,0)
,[NumberOfVendorPaymentsLT25K] = (select count( e1.PSVoucher) from [dbo].[expensedetails] e1 
									where e1.fundid = a.fundid and e1.journalID like 'AP%' 
									and e1.orderamount < 25000 and e1.accountingdate <= qs.QuarterEndDate)
 ,[VendorPaymentsLT25KAmount] = (select sum(e.orderAmount) from [dbo].[expensedetails] e 
									where e.fundid = a.fundid and e.journalID like 'AP%' 
									and e.orderamount < 25000 and e.accountingdate <= qs.QuarterEndDate)
,NumberOfSubAwardsLT25K		= coalesce(s.TotalNumberSubAardsLessThan25000,0)
,SubAwardsLT25KAmount		= coalesce(s.TotalAmountSubAardsLessThan25000,0)
,[DepartmentManagerAdmPersonID] = s.DepartmentManagerAdmPersonID
,[PreAwardManagerAdmPersonID] = s.PreAwardManagerAdmPersonID
From			[dbo].[Stg_IE_ARRA_ProposalData] s
inner join		[dbo].[ArraAgreement] a on a.ID = s.ArraAgreementID
inner join		[dbo].[ArraQuarterlyReportStatus] qs on qs.ID = a.ArraQuarterlyReportStatusID
left outer join (select fa.fundid,sum(fa.amounttodate) as AmountToDate from [dbo].[monthlyfundactuals] fa
				 where fa.monthendingdate = 
								(Select max(fa1.monthendingdate) from [dbo].[monthlyfundactuals] fa1 
								where fa.fundid = fa1.fundid and fa.financialtypeid = fa1.financialtypeid
								and fa1.monthendingDate <= @QuarterEndDate)
				and fa.FinancialTypeId in( 2,3)
				group by fa.fundid ) expenses on expenses.fundid = s.fundID
left outer join (select fa.fundid,-sum(fa.amounttodate) as AmountToDate from [dbo].[monthlyfundactuals] fa
				 where fa.monthendingdate = 
							(Select max(fa1.monthendingdate) from [dbo].[monthlyfundactuals] fa1 
							 where fa.fundid = fa1.fundid and fa.financialtypeid = fa1.financialtypeid
							 and fa1.monthendingDate <= @QuarterEndDate)
				and fa.FinancialTypeId = 1
				group by fa.fundid ) Revenue on Revenue.fundid = s.fundID
left outer join [dbo].[ArraGLClosePreview] prev on prev.fundID = s.fundID
Left outer join (select e.fundid,sum(e.Amount) as Amount 
				 from [dbo].[purencumbrance_fct] e 
				 group by e.fundid) Encumbrance on Encumbrance.FundID = s.FundID
Left outer join (Select b.fundid,sum(b.BudgetAmount) as Amount from [dbo].[agrbudget_fct] b 
				 where b.budgetviewtypeid =1 
				 group by b.fundid) budget on budget.fundid = s.fundID

/*-----------------------------------------------------------------------------
Update existing ARR Sub Award data
-----------------------------------------------------------------------------*/
Update sba
Set ArraRecipientID = coalesce(s.NewArraRecipientID,s.ArraRecipientID)
,AwardDate = isNull(s.AwardDate,a.AwardDate)
,ArraPrimaryPerformanceLocationID = coalesce(s.NewArraSubPrimaryPerformanceLocationID,s.ArraSubPrimaryPerformanceLocationID)
,BudgetAmount = s.TotalAward
,IsReportingApplicable = case when coalesce(s.SubHighlyCompensatedNames1,'')<>'' or
									coalesce(s.SubHighlyCompensatedNames2,'')<>'' or
									coalesce(s.SubHighlyCompensatedNames3,'')<>'' or
									coalesce(s.SubHighlyCompensatedNames4,'')<>'' or
									coalesce(s.SubHighlyCompensatedNames5,'')<>''
							  then 1 else 0 end
,ExpenseAmount = (select fa.amounttodate from [dbo].[monthlyfundaccountactuals] fa
					where fa.monthendingdate = (Select max(fa1.monthendingdate) from [dbo].[monthlyfundaccountactuals] fa1 
												where fa.fundid = fa1.fundid and fa.accountid = fa1.accountid
												and fa1.monthendingDate <= qs.QuarterEndDate)
						and fa.accountid = s.Finaccountid and fa.fundid = a.fundID) 
from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join [dbo].[ArraSubAward] sba on s.ArraSubAwardID = sba.ID
inner join [dbo].[ArraAgreement] a on a.ID = sba.ArraAgreementID
inner join [dbo].[ArraQuarterlyReportStatus] qs on qs.ID = a.ArraQuarterlyReportStatusID

/*-----------------------------------------------------------------------------
Insert New ARR Agreement data
-----------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraAgreement] ON

INSERT INTO [dbo].[ArraAgreement]
           ([ID]
           ,[FolderNumber]
           ,[FundID]
           ,[ArraQuarterlyReportStatusID]
           ,[FundingArraGovAgencyID]
           ,[AwardingArraGovAgencyID]
           ,[ArraProgramSourceID]
           ,[OrderNumber]
           ,[CFDANumber]
           ,[ArraRecipientID]
           ,[ParentArraRecipientID]
           ,[ArraGovContractOfficeID]
           ,[AwardType]
           ,[AwardDate]
           ,[ProjectStatus]
           ,[ArraActivityID]
           ,[ArraPrimaryPerformanceLocationID]
           ,[AwardedAmount]
           ,[RevenueAmount]
           ,[ExpenseAmount]
           ,[IsReportingApplicable]
           ,[NumberOfSubAwards]
           ,[SubAwardAmount]
           ,[NumberOfVendorPaymentsLT25K]
           ,[VendorPaymentsLT25KAmount]
           ,[NumberOfSubAwardsLT25K]
           ,[SubAwardsLT25KAmount]
           ,[DepartmentManagerAdmPersonID]
           ,[PreAwardManagerAdmPersonID])
Select s.NewArraAgreementID
,s.InfoEdProposalNumber 
,s.FundID
,s.ArraQuarterlyReportStatusID
,coalesce(s.FundingArraGovAgencyID,s.NewFundingArraGovAgencyID)
,coalesce(s.AwardingArraGovAgencyID,s.NewAwardingArraGovAgencyID)
,s.ArraProgramSourceID
,s.OrderNumber
,s.CFDA
,coalesce(s.ArraRecipientID,s.NewArraRecipientID)
,coalesce(s.ArraRecipientID,s.NewArraRecipientID)
,coalesce(s.ArraGovContractOfficeID,s.NewArraGovContractOfficeID)
,replace(s.[AwardType],'Subcontract','Grant')
,s.[NogaIssueDate]
,case when coalesce(prev.monthendingdate,'1/1/1900') <= @LastGLCloseDate then 
	case 
		When coalesce(expenses.AmountToDate+Encumbrance.Amount,0) = 0	
													Then 'Not Started'
													
		when coalesce(expenses.AmountToDate,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0))
													Then 'Fully Completed'
		
		when coalesce(expenses.AmountToDate,0) <> 0 
			and coalesce(expenses.AmountToDate,0) < (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Less than 50% completed'
													
		when coalesce(expenses.AmountToDate,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Completed 50% or more'
	end
Else
	case 
		When coalesce(prev.ExpenseAmount,0) = 0	
													Then 'Not Started'
													
		when coalesce(prev.ExpenseAmount,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0))
													Then 'Fully Completed'
		when coalesce(prev.ExpenseAmount,0) <> 0 
			and coalesce(prev.ExpenseAmount,0) < (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Less than 50% completed'
													
		when coalesce(prev.ExpenseAmount,0) >= (coalesce(s.AmountOfAward,Budget.Amount,0) * .5)
													Then 'Completed 50% or more'
	end
End
,coalesce(s.ArraActivityID,s.NewArraActivityID)
,coalesce(s.ArraPrimaryPerformanceLocationID,s.NewArraPrimaryPerformanceLocationID)
,coalesce(s.AmountOfAward,0)
,case when coalesce(prev.monthendingdate,'1/1/1900') <= @LastGLCloseDate then coalesce(Revenue.AmountToDate,0) 
								else
									prev.RevenueAmount 
								end
,case when coalesce(prev.monthendingdate,'1/1/1900') <= @LastGLCloseDate then coalesce(expenses.AmountToDate,0)
								else
									prev.ExpenseAmount 
								end
,0--[IsReportingApplicable]
,coalesce(s.TotalNumberSubAards,0)
,coalesce(s.TotalAmountSubAards,0)
,coalesce((select count( e1.PSVoucher) from [dbo].[expensedetails] e1 
			where e1.fundid = s.fundid and e1.journalID like 'AP%' and e1.orderamount < 25000 
			and e1.accountingdate <= qs.QuarterEndDate),0)
,coalesce((select sum(e.orderAmount) from [dbo].[expensedetails] e 
			where e.fundid = s.fundid and e.journalID like 'AP%' and e.orderamount < 25000 
			and e.accountingdate <= qs.QuarterEndDate),0)
,coalesce(s.TotalNumberSubAardsLessThan25000,0)
,coalesce(s.TotalAmountSubAardsLessThan25000,0)
,DepartmentManagerAdmPersonID
,PreAwardManagerAdmPersonID
From			[dbo].[Stg_IE_ARRA_ProposalData] s
inner join		[dbo].[ArraQuarterlyReportStatus] qs on qs.ID = s.ArraQuarterlyReportStatusID
left outer join (select fa.fundid,sum(fa.amounttodate) as AmountToDate from [dbo].[monthlyfundactuals] fa
				 where fa.monthendingdate = 
								(Select max(fa1.monthendingdate) from [dbo].[monthlyfundactuals] fa1 
								where fa.fundid = fa1.fundid and fa.financialtypeid = fa1.financialtypeid
								and fa1.monthendingDate <= @QuarterEndDate)
				and fa.FinancialTypeId in( 2,3)
				group by fa.fundid ) expenses on expenses.fundid = s.fundID
left outer join (select fa.fundid,-sum(fa.amounttodate) as AmountToDate from [dbo].[monthlyfundactuals] fa
				 where fa.monthendingdate = 
							(Select max(fa1.monthendingdate) from [dbo].[monthlyfundactuals] fa1 
							 where fa.fundid = fa1.fundid and fa.financialtypeid = fa1.financialtypeid
							 and fa1.monthendingDate <= @QuarterEndDate)
				and fa.FinancialTypeId = 1
				group by fa.fundid ) Revenue on Revenue.fundid = s.fundID
left outer join [dbo].[ArraGLClosePreview] prev on prev.fundID = s.fundID
Left outer join (select e.fundid,sum(e.Amount) as Amount 
				 from [dbo].[purencumbrance_fct] e 
				 group by e.fundid) Encumbrance on Encumbrance.FundID = s.FundID
Left outer join (Select b.fundid,sum(b.BudgetAmount) as Amount from [dbo].[agrbudget_fct] b 
				 where b.budgetviewtypeid =1 
				 group by b.fundid) budget on budget.fundid = s.fundID
where s.NewArraAgreementID is not null

Set Identity_insert [dbo].[ArraAgreement] Off

/*-----------------------------------------------------------------------------
Insert New ARR Sub Award data
-----------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraSubAward] ON

INSERT INTO [dbo].[ArraSubAward](
ID
,ArraRecipientID
,ArraAgreementID
,FinAccountID
,AwardDate
,ArraPrimaryPerformanceLocationID
,BudgetAmount
,IsReportingApplicable
,ExpenseAmount
)
select s.NewArraSubAwardID
,coalesce(s.NewArraRecipientID,s.ArraRecipientID)
,s.ArraAgreementID
,s.FinAccountID
,IsNull(s.AwardDate,sp.[NogaIssueDate])
,coalesce(s.NewArraSubPrimaryPerformanceLocationID,s.ArraSubPrimaryPerformanceLocationID)
,s.TotalAward
,case when coalesce(s.SubHighlyCompensatedNames1,'')<>'' or
			coalesce(s.SubHighlyCompensatedNames2,'')<>'' or
			coalesce(s.SubHighlyCompensatedNames3,'')<>'' or
			coalesce(s.SubHighlyCompensatedNames4,'')<>'' or
			coalesce(s.SubHighlyCompensatedNames5,'')<>''
	  then 1 else 0 end
,ExpenseAmount = (select fa.amounttodate from [dbo].[monthlyfundaccountactuals] fa
					where fa.monthendingdate = (Select max(fa1.monthendingdate) from [dbo].[monthlyfundaccountactuals] fa1 
												where fa.fundid = fa1.fundid and fa.accountid = fa1.accountid
												and fa1.monthendingDate <= qs.QuarterEndDate)
						and fa.accountid = s.Finaccountid and fa.fundid = sp.fundID) 
from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join [dbo].[Stg_IE_ARRA_ProposalData] sp on coalesce(sp.ArraAgreementID,sp.NewArraAgreementID) = s.ArraAgreementID
inner join [dbo].[ArraQuarterlyReportStatus] qs on qs.ID = sp.ArraQuarterlyReportStatusID
where s.NewArraSubAwardID is not null

Set Identity_insert [dbo].[ArraSubAward] Off

/*-----------------------------------------------------------------------------
Truncate ARR Sub Award Highly Compensated Person Data
-----------------------------------------------------------------------------*/
Truncate Table [dbo].[ArraHighlyCompensatedPerson]

/*-----------------------------------------------------------------------------
Insert New ARR Sub Award Highly Compensated Person Data
-----------------------------------------------------------------------------*/
INSERT INTO [dbo].[ArraHighlyCompensatedPerson]
           ([ArraAgreementID]
           ,[ArraSubAwardID]
           ,[PersonName]
           ,[TotalCompensation])
Select  s.ArraAgreementID, coalesce(s.NewArraSubAwardID,s.ArraSubAwardID), s.SubHighlyCompensatedNames1
		, s.SubHighlyCompensatedCompensation1 from [dbo].[Stg_IE_ARRA_SubContractorData] s 
where coalesce(s.SubHighlyCompensatedNames1,'')<>'' 
		and s.ArraAgreementID is not null and coalesce(s.NewArraSubAwardID,s.ArraSubAwardID) is not null
union all
Select  s.ArraAgreementID, coalesce(s.NewArraSubAwardID,s.ArraSubAwardID), s.SubHighlyCompensatedNames2
			, s.SubHighlyCompensatedCompensation2 from [dbo].[Stg_IE_ARRA_SubContractorData] s 
where coalesce(s.SubHighlyCompensatedNames2,'')<>'' 
		and s.ArraAgreementID is not null and coalesce(s.NewArraSubAwardID,s.ArraSubAwardID) is not null
union all
Select  s.ArraAgreementID, coalesce(s.NewArraSubAwardID,s.ArraSubAwardID), s.SubHighlyCompensatedNames3
			, s.SubHighlyCompensatedCompensation3 from [dbo].[Stg_IE_ARRA_SubContractorData] s 
where coalesce(s.SubHighlyCompensatedNames3,'')<>'' 
		and s.ArraAgreementID is not null and coalesce(s.NewArraSubAwardID,s.ArraSubAwardID) is not null
union all
Select  s.ArraAgreementID, coalesce(s.NewArraSubAwardID,s.ArraSubAwardID), s.SubHighlyCompensatedNames4
			, s.SubHighlyCompensatedCompensation4 from [dbo].[Stg_IE_ARRA_SubContractorData] s 
where coalesce(s.SubHighlyCompensatedNames4,'')<>'' 
		and s.ArraAgreementID is not null and coalesce(s.NewArraSubAwardID,s.ArraSubAwardID) is not null
union all
Select  s.ArraAgreementID, coalesce(s.NewArraSubAwardID,s.ArraSubAwardID), s.SubHighlyCompensatedNames5
			, s.SubHighlyCompensatedCompensation5 from [dbo].[Stg_IE_ARRA_SubContractorData] s 
where coalesce(s.SubHighlyCompensatedNames5,'')<>'' 
		and s.ArraAgreementID is not null and coalesce(s.NewArraSubAwardID,s.ArraSubAwardID) is not null
		
/*-----------------------------------------------------------------------------
Insert New ARR Questionnaires.  If there is data for a prior quarter, it is
CLONED into the narrative and the quarterly activities columns.
-----------------------------------------------------------------------------*/
Set Identity_insert [dbo].[ArraPIQuestionnaire] ON

insert into [dbo].[ArraPIQuestionnaire](ID,ArraAgreementID,Narrative,QuarterlyActivities)
select s.NewArraAgreementID,s.NewArraAgreementID,coalesce(clonedQuestr.Narrative,''),coalesce(clonedQuestr.QuarterlyActivities,'') 
From [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraQuarterlyReportStatus] CurrentQuarter on CurrentQuarter.ID = s.ArraQuarterlyReportStatusID
left outer join [dbo].[ArraQuarterlyReportStatus] PriorQuarter on PriorQuarter.ID = 
															(Select max(q1.ID) from [dbo].[ArraQuarterlyReportStatus] q1
															 where q1.QuarterEndDate < CurrentQuarter.QuarterEndDate)
left outer join [dbo].[ArraAgreement] clonedAgr on clonedAgr.Fundid = s.fundID 
													and clonedAgr.ArraQuarterlyReportStatusID = PriorQuarter.ID
left outer join [dbo].[ArraPIQuestionnaire] clonedQuestr on clonedQuestr.ID = clonedAgr.id
where s.NewArraAgreementID is not null
	and not exists(select 1 from [dbo].[ArraPIQuestionnaire] q
					where s.NewArraAgreementID = q.ArraAgreementID)

Set Identity_insert [dbo].[ArraPIQuestionnaire] OFF

	
/*-----------------------------------------------------------------------------
CLONE ARR Questionnaire Jobs.  If there is Job data for a prior quarter, it is
CLONED into the ArraPIQuestionnaireJob table.
-----------------------------------------------------------------------------*/

insert into [dbo].[ArraPIQuestionnaireJob](ArraPIQuestionnaireID,ArraPIQuestionnaireJobTypeID,JobTitle,PercentEffort)
select s.NewArraAgreementID,clonedQuestr.ArraPIQuestionnaireJobTypeID,clonedQuestr.JobTitle,clonedQuestr.PercentEffort
From [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraQuarterlyReportStatus] CurrentQuarter on CurrentQuarter.ID = s.ArraQuarterlyReportStatusID
left outer join [dbo].[ArraQuarterlyReportStatus] PriorQuarter on PriorQuarter.ID = 
															(Select max(q1.ID) from [dbo].[ArraQuarterlyReportStatus] q1
															 where q1.QuarterEndDate < CurrentQuarter.QuarterEndDate)
left outer join [dbo].[ArraAgreement] clonedAgr on clonedAgr.Fundid = s.fundID 
													and clonedAgr.ArraQuarterlyReportStatusID = PriorQuarter.ID
left outer join [dbo].[ArraPIQuestionnaireJob] clonedQuestr on clonedQuestr.ArraPIQuestionnaireID = clonedAgr.id
where s.NewArraAgreementID is not null and clonedQuestr.ID is not null



/*-----------------------------------------------------------------------------
Insert Start Event for New ARR Questionnaires
-----------------------------------------------------------------------------*/
insert into [dbo].[ArraPIQuestionnaireEventHistory](ArraPIQuestionnaireID,EventDate,ArraPIQuestionnaireEventID,CreatedByAdmPersonID)
select s.NewArraAgreementID,getdate()
	, ev.ID
	,null
From [dbo].[Stg_IE_ARRA_ProposalData] s
cross join (select e.id from [dbo].[ArraPIQuestionnaireEvent] e
		inner join [dbo].[ArraPIQuestionnaireEventType] et on et.id = e.ArraPIQuestionnaireEventTypeID
		where et.[Description]='State Change' and e.[name] like 'Start%') ev
where s.NewArraAgreementID is not null 
	and not exists(select 1 from [dbo].[ArraPIQuestionnaireEventHistory] h
					where s.NewArraAgreementID = h.ArraPIQuestionnaireID and ev.id = h.ArraPIQuestionnaireEventID)

Commit Transaction InjectArraAgreementData

End Try

Begin Catch
Declare 
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
Select 
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original
-- error information.
Select @ErrorMessage = 
    N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
        'Message: '+ ERROR_MESSAGE();

If XACT_STATE() <> 0 Rollback Transaction InjectArraAgreementData

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
Raiserror
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1,               
    @ErrorNumber,    -- parameter: original error number.
    @ErrorSeverity,  -- parameter: original error severity.
    @ErrorState,     -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine       -- parameter: original error line number.
    );
End Catch;

END 
GO 
